﻿CREATE PROC [admin].[MakeSecurityTrigger]
	@TableName sysname
AS
DECLARE @SQL nvarchar(max),@TableBase sysname,@TriggerName sysname
SET @TableBase = RIGHT(@TableName,LEN(@TableName)-CHARINDEX('_',@TableName))
SET @TriggerName = 'trs_'+@TableBase
IF OBJECT_ID(@TriggerName) > 0 SET @SQL = 'ALTER' ELSE SET @SQL = 'CREATE'
SET @SQL = @SQL + ' TRIGGER [dbo].['+@TriggerName+']
ON [dbo].['+@TableName+']
FOR INSERT,UPDATE,DELETE
AS

IF @@ROWCOUNT = 0 RETURN
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN
SET NOCOUNT ON

--DECLARE @LoanID int SELECT @LoanID = ID FROM inserted WHERE StatusID = 0 IF NOT @LoanID IS NULL BEGIN	ROLLBACK	RAISERROR('''+@TableBase+':%d can not have StatusID set to Zero'',16,1,@LoanID)	RETURN END

IF UPDATE(StatusID) AND NOT(UPDATE(StatusDate) AND UPDATE(StatusUser))
	UPDATE '+@TableName+' SET StatusDate = GETDATE(),StatusUser = [dbo].[UserName]()
	FROM '+@TableName+' RC WITH(nolock) INNER JOIN inserted i ON RC.ID = i.ID
	LEFT OUTER JOIN deleted d ON d.ID = i.ID
	WHERE d.StatusID IS NULL OR d.StatusID <> i.StatusID

DECLARE @AccessID int,@RowCount int,@StatusID int
CREATE TABLE #StatusIDs(StatusID int NOT NULL)
IF NOT EXISTS(SELECT * FROM inserted)BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM deleted
	SELECT @RowCount = COUNT(*),@AccessID = 4 -- Delete
END
ELSE IF NOT EXISTS(SELECT * FROM deleted ) BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM inserted
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 8 -- Add
END
ELSE BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM deleted
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END

SELECT @StatusID = StatusID FROM (SELECT StatusID FROM inserted UNION SELECT StatusID FROM deleted)T

/*T*/IF dbo.Config_Trace() = 1 BEGIN SELECT * INTO #inserted FROM inserted DECLARE @CU varbinary(999) SET @CU=COLUMNS_UPDATED()EXEC sp__TraceTrigger @@PROCID,@CU END/*T*/

DECLARE @CUs varbinary(1024) SET @CUs = COLUMNS_UPDATED()

EXEC sec__CheckTableAccess @RowCount,@CUs,@@PROCID,@StatusID,@AccessID

IF @@ERROR <> 0 RETURN

IF NOT UPDATE (StatusID) AND EXISTS(SELECT * FROM deleted) RETURN

INSERT INTO tlog_State(TablePK,TableName,StateFrom,StateTo)
SELECT i.ID,'''+@TableName+''',ISNULL(d.StatusID,0),i.StatusID
FROM inserted i LEFT OUTER JOIN deleted d on i.ID = d.ID
WHERE i.StatusID <> ISNULL(d.StatusID,0)

IF @@ROWCOUNT = 0
INSERT INTO tlog_State(TablePK,TableName,StateFrom,StateTo)
SELECT ID,'''+@TableName+''',0,StatusID FROM inserted
'
PRINT @SQL
EXEC (@SQL)
PRINT '--------------------------------------------------------------------------------------'









